﻿using QG.FrontDesk.Core;
using QG.FrontDesk.DAL.IRepository;
using QG.FrontDesk.Model;
using System;
using System.Collections.Generic;

namespace QG.FrontDesk.DAL.Repository
{
    public class PositionInfoRepository : IPositionInfoRepository
    {
        public List<PositionInfo> GetPositionInfos(string type, string word, Nullable<int> ClassId, Nullable<int> level)
        {
            List<PositionInfo> positionInfos = null;
            string sql = @"select * from positionInfo";
            if (level == 2)
            {
                sql += " WHERE postNameId IN (SELECT postNameId FROM postName WHERE classId=@classId)";
                positionInfos = DapperDBHelper<PositionInfo>.QueryList(sql, new { classId = ClassId });

            }
            else if (level == 3)
            {
                sql += " WHERE postNameId=@classId ";
                positionInfos = DapperDBHelper<PositionInfo>.QueryList(sql, new { classId = ClassId });
            }
            else if (!string.IsNullOrEmpty(word))
            {
                word = "%" + word + "%";
                if (type == "1")
                {
                    sql += " WHERE postNameId IN (SELECT postNameId FROM postName WHERE NAME LIKE @word)";

                }
                else
                {
                    sql += " WHERE PostId IN (SELECT PostId FROM positionInfo WHERE compId IN (SELECT compId FROM company WHERE comFullName LIKE @word) )";
                }
                positionInfos = DapperDBHelper<PositionInfo>.QueryList(sql, new { word = word });
            }
            else
            {
                positionInfos = DapperDBHelper<PositionInfo>.QueryList(sql, null);
            }
            foreach (var item in positionInfos)
            {
                //得到职位名称
                sql = "SELECT * FROM postName WHERE postNameId=(SELECT postNameId FROM positionInfo WHERE PostId=@PostId)";
                item.Postname = DapperDBHelper<PostName>.QueryFirstOrDefault(sql, new { PostId = item.PostId });
                //得到公司信息
                sql = @"SELECT * FROM company
                WHERE compId = (SELECT compId FROM positioninfo WHERE postId = @postId )";
                item.Company = DapperDBHelper<Company>.QueryFirstOrDefault(sql, new { postId = item.PostId });
                //得到城市信息
                sql = "SELECT * FROM cityInfo WHERE cityId =@cityId ";
                item.Cityinfo = DapperDBHelper<Cityinfo>.QueryFirstOrDefault(sql, new { cityId = item.CityId });
                //查找对应的标签
                sql = @"SELECT * FROM company_Label
                    INNER JOIN label ON company_Label.`labelId`= label.`labelId`
                    WHERE compId = @CompId";
                item.Company.Company_label = DapperDBHelper<Company_Label>.QueryList(sql, new { CompId = item.Company.CompId });
                //查找对应的产品信息
                sql = @"SELECT * FROM productInfo WHERE compId= @CompId";
                item.Company.ProductInfo = DapperDBHelper<ProductInfo>.QueryList(sql, new { CompId = item.Company.CompId });
                //查找对应的简历信息
                sql = "SELECT * FROM Positioninfo WHERE compId=@CompId";
                item.Company.PositionInfo = DapperDBHelper<PositionInfo>.QueryList(sql, new { CompId = item.Company.CompId });
                //查询简历对应的城市和职位名称
                foreach (var item1 in item.Company.PositionInfo)
                {
                    sql = "SELECT cityName FROM cityInfo WHERE cityId=@cityId";
                    item1.Cityinfo = DapperDBHelper<Cityinfo>.QueryFirstOrDefault(sql, new { cityId = item1.CityId });

                    sql = "SELECT NAME FROM postName WHERE postNameId=@postNameId";
                    item1.Postname = DapperDBHelper<PostName>.QueryFirstOrDefault(sql, new { postNameId = item1.PostNameId });
                }
                //查找公司创始人团队
                sql = "SELECT * FROM FoundTeam WHERE compId=@CompId";
                item.Company.FoundTeam = DapperDBHelper<FoundTeam>.QueryList(sql, new { CompId = item.Company.CompId });
                //查找行业领域
                sql = "SELECT * FROM industry WHERE industryId=@industryId";
                item.Company.Industry = DapperDBHelper<Industry>.QueryFirstOrDefault(sql, new { industryId = item.Company.IndustryId });
                //查找发展阶段
                sql = "SELECT * FROM developmentPhase WHERE phaseId=@phaseId";
                item.Company.DevelopmentPhase = DapperDBHelper<DevelopmentPhase>.QueryFirstOrDefault(sql, new { phaseId = item.Company.PhaseId });
            }
            return positionInfos;
        }
        /// <summary>
        /// 得到对应的职位信息
        /// </summary>
        /// <param name="CompId"></param>
        /// <param name="statu"></param>
        /// <returns></returns>
        public List<PositionInfo> GetPosition(int CompId, bool statu)
        {
            string sql = "SELECT * FROM positionInfo WHERE compId=@CompId AND Statu=@Statu";
            List<PositionInfo> positionInfos = DapperDBHelper<PositionInfo>.QueryList(sql, new { CompId = CompId, Statu = statu });
            foreach (var item in positionInfos)
            {
                //得到职位名称
                sql = "SELECT * FROM postName WHERE postNameId=(SELECT postNameId FROM positionInfo WHERE PostId=@PostId)";
                item.Postname = DapperDBHelper<PostName>.QueryFirstOrDefault(sql, new { PostId = item.PostId });
                //得到公司信息
                sql = @"SELECT * FROM company
                WHERE compId = (SELECT compId FROM positioninfo WHERE postId = @postId )";
                item.Company = DapperDBHelper<Company>.QueryFirstOrDefault(sql, new { postId = item.PostId });
                //得到城市信息
                sql = "SELECT * FROM cityInfo WHERE cityId =@cityId ";
                item.Company.CityInfo = DapperDBHelper<Cityinfo>.QueryFirstOrDefault(sql, new { cityId = item.Company.CityId });
                //查找行业领域
                sql = "SELECT * FROM industry WHERE industryId=@industryId";
                item.Company.Industry = DapperDBHelper<Industry>.QueryFirstOrDefault(sql, new { industryId = item.Company.IndustryId });
                //查找发展阶段
                sql = "SELECT * FROM developmentPhase WHERE phaseId=@phaseId";
                item.Company.DevelopmentPhase = DapperDBHelper<DevelopmentPhase>.QueryFirstOrDefault(sql, new { phaseId = item.Company.PhaseId });
                //查找职位下的简历
                sql = "SELECT * FROM position_resumeinfo WHERE PostId=@PostId";
                item.Position_ResumeInfo = DapperDBHelper<Position_ResumeInfo>.QueryList(sql, new { PostId = item.PostId });
            }
            return positionInfos;
        }
        /// <summary>
        /// 修改职位状态
        /// </summary>
        /// <param name="id"></param>
        /// <param name="statu"></param>
        /// <returns></returns>
        public int UpdatePositionStatu(int id, bool statu)
        {
            string sql = "UPDATE positionInfo SET statu=@statu WHERE PostId=@PostId";
            return DapperDBHelper<PositionInfo>.Execute(sql, new { PostId = id, statu = statu });
        }

    }
}
